package com.ssyt.yt1.day25;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;

import com.ssyt.yt1.common.util.DBUtil;
import com.ssyt.yt1.common.util.Util;

/**
 * <pre>
 * 事务操作：要么成功，要么失败！
 * 1、关闭MySQL的自动提交
 * 2、使用InnoDB的数据库引擎
 * 
 * 注：事务操作只针对于增、删、改！
 * </pre>
 * 
 * @author Hsu.
 * @since 2015年12月28日
 */
public class TransactionDemo {

	/**
	 * 添加一个用户，并添加这个用户所对应的教育经历！
	 * 
	 * @param args
	 * @throws ParseException
	 */
	public static void main(String[] args) throws ParseException {
		Connection connection = null;
		PreparedStatement preparedStatement1 = null;
		PreparedStatement preparedStatement2 = null;
		PreparedStatement preparedStatement3 = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			// 关闭数据库的自动提交
			connection.setAutoCommit(false);

			// 1、添加一个用户
			User user = new User();
			user.setIdcard("12345");
			user.setUsername("王五");
			user.setPassword("admin");
			user.setSalary(new BigDecimal("999.90"));
			user.setBirthday(Util.parse("1995-12-11", "yyyy-MM-dd"));
			user.setMemo(".........");
			String sql = "insert into user values(null, ?, ?, ?, ?, ?, ?)";
			preparedStatement1 = connection.prepareStatement(sql);
			preparedStatement1.setString(1, user.getIdcard());
			preparedStatement1.setString(2, user.getUsername());
			preparedStatement1.setString(3, user.getPassword());
			preparedStatement1.setBigDecimal(4, user.getSalary());
			preparedStatement1.setTimestamp(5, new Timestamp(user.getBirthday()
					.getTime()));
			preparedStatement1.setString(6, user.getMemo());
			preparedStatement1.executeUpdate();

			// 2、得到刚添加的用户对应的id
			String sql2 = "select id from user where idcard=?";
			preparedStatement2 = connection.prepareStatement(sql2);
			preparedStatement2.setString(1, user.getIdcard());
			resultSet = preparedStatement2.executeQuery();
			resultSet.next();
			int userId = resultSet.getInt("id");

			// 3、添加用户所对应的教育经历
			String sql3 = "insert into school value2s(null, ?, ?)";
			preparedStatement3 = connection.prepareStatement(sql3);
			preparedStatement3.setString(1, "清华大学");
			preparedStatement3.setInt(2, userId);
			preparedStatement3.executeUpdate();

			// 事务完成，提交事务
			connection.commit();

			System.out.println("信息添加成功！");
		} catch (SQLException e) {
			// try {
			// connection.rollback();
			// } catch (SQLException e1) {
			// e1.printStackTrace();
			// }
			System.out.println("信息添加失败！");
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet);
			DBUtil.close(preparedStatement1);
			DBUtil.close(preparedStatement2);
			DBUtil.close(preparedStatement3);
			DBUtil.close(connection);
		}
	}
}
